﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class draw : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        int capacity,sum,min;//前者表示课程容量，后者表示课程已选人数，选取两者中小者进行循环
        int i;
        string Sno,Cno,str_sql;

        string conn = "Data Source= . ;Initial Catalog=XSXK; User ID=sa; pwd=123";
        SqlConnection connection = new SqlConnection(conn);

        try
        {
            connection.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = connection;

            str_sql = " select Cno from Course where Cname='" + DropDownList1.Text + "' ";
            comm.CommandText = str_sql;
            Cno = comm.ExecuteScalar().ToString();//课程号

            str_sql = " select capacity from Course where Cname='"+DropDownList1.Text+"' ";
            comm.CommandText = str_sql;
            capacity = Convert.ToInt16(comm.ExecuteScalar());//课程容量

            str_sql = " select count(*) from select_course_temp where Cno='"+Cno+"' ";
            comm.CommandText = str_sql;
            sum = Convert.ToInt16(comm.ExecuteScalar());//这门课的选课人数

            min = capacity < sum ? capacity : sum;

            for (i = 0; i < min; i++)
            {
                str_sql = " select Sno from Select_course_temp where Select_course_temp.Cno='" + Cno + "' ";//取出第一个选这门课的学号
                comm.CommandText = str_sql;
                Sno = Convert.ToString(comm.ExecuteScalar());

                str_sql = " insert into Select_course(Sno,Cno) values('"+Sno+"','"+Cno+"') ";//将学号、课程号从临时表插入选课表
                comm.CommandText = str_sql;
                comm.ExecuteNonQuery();

                str_sql = " delete from Select_course_temp where Sno='"+Sno+"' and Cno='"+Cno+"' ";//删除临时表中的第一条选课信息
                comm.CommandText = str_sql;
                comm.ExecuteNonQuery();

            }          
            
            Response.Write("<h1>成功抽签！</h1>");

            str_sql = " delete from Select_course_temp where Cno='"+Cno+"' ";//删除临时表中，关于课程Cno的剩余未选上的选课信息
            comm.CommandText = str_sql;
            comm.ExecuteNonQuery();
            
        }

        catch (SqlException ex)
        {
            Response.Write(ex.Message);
        }

        connection.Close();//关闭数据库连接
    }
}